package com.my.lucky.repository;

import com.my.lucky.entity.StockQueryRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface StockQueryRecordRepository extends JpaRepository<StockQueryRecord, Long> {

    /**
     * 根据类型查询股票查询记录，并去重股票代码
     *
     * @param types 类型列表
     * @return 去重后的股票代码列表
     */
    @Query("SELECT DISTINCT sqr.stockCode FROM StockQueryRecord sqr WHERE sqr.type IN ?1")
    List<String> findDistinctStockCodesByTypeIn(List<Integer> types);

    List<StockQueryRecord> findByWencaiKeywordId(Long id);

    List<StockQueryRecord> findByWencaiKeywordIdOrderByCreateTimeDesc(Long id);

    @Query("SELECT sqr FROM StockQueryRecord sqr WHERE sqr.wencaiKeyword.id = ?1 AND sqr.queryTime = ?2")
    List<StockQueryRecord> findLatestRecordsByWencaiKeywordId(Long id, LocalDateTime maxQueryTime);

    @Query("SELECT sqr FROM StockQueryRecord sqr WHERE sqr.wencaiKeyword.id = ?1 AND sqr.queryTime <?2 AND sqr.queryTime>?3 ")
    List<StockQueryRecord> findSecondLatestRecordsByWencaiKeywordId(Long id, LocalDateTime maxQueryTime, LocalDateTime nowDay);

    @Query("SELECT MAX(sqr.queryTime) FROM StockQueryRecord sqr WHERE sqr.wencaiKeyword.id = ?1")
    LocalDateTime findMaxQueryTimeByWencaiKeywordId(Long id);

    /**
     * 根据查询时间范围查询股票查询记录
     */
    List<StockQueryRecord> findByQueryTimeBetweenOrderByQueryTimeAsc(LocalDateTime startTime, LocalDateTime endTime);

    /**
     * 根据查询时间范围和类型查询股票查询记录
     */
    List<StockQueryRecord> findByQueryTimeBetweenAndTypeOrderByQueryTimeAsc(LocalDateTime startTime, LocalDateTime endTime, Integer type);


    @Query("SELECT s.stockCode as stockCode,MAX(s.queryTime) as queryTime, COUNT(s) as queryCount " +
            "FROM StockQueryRecord s " +
            "WHERE s.queryTime BETWEEN :startTime AND :endTime " +
            "GROUP BY s.stockCode " +
            "ORDER BY queryCount DESC")
    List<StockQueryRecord> findStockQueryStats(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);


    /**
     * 2个日期取交集
     *
     * @param fstartDateTime
     * @param fendDateTime
     * @param SstartDateTime
     * @param SendDateTime
     * @return
     */
    @Query("SELECT sqr FROM StockQueryRecord sqr WHERE sqr.queryTime >?1 AND sqr.queryTime<?2 and sqr.stockCode in (SELECT sq.stockCode FROM StockQueryRecord sq WHERE sq.queryTime >?3 AND sq.queryTime<?4)")
    List<StockQueryRecord> findIntersectionRecords(LocalDateTime fstartDateTime, LocalDateTime fendDateTime, LocalDateTime SstartDateTime, LocalDateTime SendDateTime);

    /**
     * 2个日期取差值
     *
     * @param fstartDateTime
     * @param fendDateTime
     * @param SstartDateTime
     * @param SendDateTime
     * @return
     */
    @Query("SELECT sqr FROM StockQueryRecord sqr WHERE sqr.queryTime >?1 AND sqr.queryTime<?2 and sqr.stockCode not in (SELECT sq.stockCode FROM StockQueryRecord sq WHERE sq.queryTime >?3 AND sq.queryTime<?4)")
    List<StockQueryRecord> findFDifferenceRecords(LocalDateTime fstartDateTime, LocalDateTime fendDateTime, LocalDateTime SstartDateTime, LocalDateTime SendDateTime);

    /**
     * 有涨停的
     *
     * @param fstartDateTime
     * @param fendDateTime
     * @return
     */
    @Transactional(readOnly = true)
    @Query("SELECT sqr FROM StockQueryRecord sqr WHERE sqr.queryTime >?1 AND sqr.queryTime<?2 and sqr.stockCode in (SELECT sq.stockCode FROM StockLimitUp sq WHERE sq.createTime >?1 AND sq.createTime<?2)")
    List<StockQueryRecord> findLimitedRecords(LocalDateTime fstartDateTime, LocalDateTime fendDateTime);


    @Modifying
    @javax.transaction.Transactional
    @Query("UPDATE StockQueryRecord  set rankPosition = ?1,oldRankPosition = ?2 WHERE queryTime > CURRENT_DATE and stockCode= ?3")
    void updateByqueryTimeAfterToday(Long rankPosition, Long oldRankPosition, String stockCode);

//    @Modifying
//    @javax.transaction.Transactional
//    @Query("UPDATE StockQueryRecord sqr,Stocks s SET sqr.a = s.b WHERE sqr.stockCode = s.stockCode and s.createTime > CURRENT_DATE  and sqr.createTime > CURRENT_DATE")
//    void updateByqueryTimeAfterTodayFromStocks();


    @Modifying
    @Transactional
    @Query(value = "UPDATE stock_query_record sqr " +
            "JOIN stocks s ON sqr.stock_code = s.code " +
            "SET sqr.high_price = s.high_price,sqr.low_price = s.low_price,sqr.open_price = s.open_price," +
            " sqr.rank_position = s.rank_position,sqr.old_rank_position = s.old_rank_position,sqr.main_capital_flow = s.main_capital_flow,sqr.capital_flow = s.capital_flow " +
            "WHERE s.create_time > CURRENT_DATE AND sqr.create_time > CURRENT_DATE", nativeQuery = true)
    void updateByQueryTimeAfterTodayFromStocks();

    @Modifying
    @Transactional
    @Query(value = "UPDATE stock_query_record sqr " +
            "JOIN stocks s ON sqr.stock_code = s.code " +
            "SET sqr.rank_position = s.rank_position,sqr.old_rank_position = s.old_rank_position,sqr.main_capital_flow = s.main_capital_flow,sqr.capital_flow = s.capital_flow ,sqr.main_cost = s.main_cost  ,sqr.all_concept = s.concept " +
            "WHERE s.create_time > CURRENT_DATE AND sqr.create_time > CURRENT_DATE", nativeQuery = true)
    void updateByQueryTimeAfterTodayFromStocksFlow();


//    List<StockQueryRecord> findLimitedRecords(LocalDateTime start, LocalDateTime end);

//    @Query("SELECT s.stockCode, MAX(s.queryTime), COUNT(s) ,s.wencaiKeyword " +
//            "FROM StockQueryRecord s " +
//            "WHERE s.queryTime BETWEEN :startTime AND :endTime " +
//            "GROUP BY s.stockCode,s.wencaiKeyword " +
//            "ORDER BY COUNT(s) DESC")
//    List<Object[]> findStockQueryStats(@Param("startTime") LocalDateTime startTime,
//                                       @Param("endTime") LocalDateTime endTime);


}